﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Dal
{
    public class BaseCRUDDatabase
    {
        /*
         * 
         * File:BaseCRUDDatabase.cs
         * 
         * Performance:To perform the database, delete, update, insert & select function the base class
         * 
         * Time:2011-08-20
         * 
         * Author:wangyufei 王羽飞
         * 
         */

        #region For performing the function database query the base class

        /// <summary>
        /// According to the column name inquires the database
        /// </summary>
        /// <param name="column">column array</param>
        /// <param name="tablename">table name</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string[] column, string tablename)
        {
            string sql = "select " + Get_name_array_sum(column, Get_name_array_sum_switch.to_common) + "from " + tablename;
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return null;
            }

        }
        /// <summary>
        /// According to the column name and conditions inquires the database
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="column">column array</param>
        /// <param name="conditions">conditions array</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string tablename, string[] column, string[] conditions)
        {
            string sql = "select @column from " + tablename + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
            SqlParameter[] sqlpar ={
                                    new SqlParameter("@column",Get_name_array_sum(column,Get_name_array_sum_switch.to_common))
                                  };
            try
            {
                DataTable dataset = DBHelper.DataAdapter(sql, CommandType.Text, sqlpar);

                return dataset;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return null;
            }

        }
        /// <summary>
        /// From a database table inquires the all the data
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string tablename)
        {
            string sql = "select * from " + tablename;
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }

        }
        /// <summary>
        /// According to the condition from a database table inquires the all the data
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="conditions">conditions array</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string tablename, string[] conditions)
        {
            string sql = "select * from " + tablename + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }
        /// <summary>
        /// Connection of the conditions of the control method
        /// </summary>
        private enum Get_name_array_sum_switch
        {
            to_common,
            to_and,
            to_or
        }
        /// <summary>
        /// Decomposition form a continuous name of a string
        /// </summary>
        /// <param name="column_arr">column array</param>
        /// <returns>string</returns>
        private static string Get_name_array_sum(string[] name_arr, Get_name_array_sum_switch getnamearraysumswitch)
        {
            string namesum = "";

            switch (getnamearraysumswitch)
            {
                case Get_name_array_sum_switch.to_common:
                    for (int i = 0; i < name_arr.Length; i++)
                    {
                        namesum += name_arr[i] + ", ";
                    }
                    namesum = namesum.Substring(0, namesum.Length - 2);
                    namesum = namesum + " ";
                    break;
                case Get_name_array_sum_switch.to_and:
                    for (int i = 0; i < name_arr.Length; i++)
                    {
                        namesum += name_arr[i] + " and ";
                    }
                    namesum = namesum.Substring(0, namesum.Length - 4);
                    break;
                case Get_name_array_sum_switch.to_or:
                    for (int i = 0; i < name_arr.Length; i++)
                    {
                        namesum += name_arr[i] + " or ";
                    }
                    namesum = namesum.Substring(0, namesum.Length - 4);
                    break;
            }
            return namesum;
        }
        /// <summary>
        /// A common enumeration type
        /// </summary>
        public enum Type_of_execute
        {
            count,
            topasc,
            topdesc
        }
        /// <summary>
        /// From a database table inquires the count of all the data
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="typeofexecute">enum value</param>
        /// <returns>int</returns>
        public static int DatabaseSelect(string tablename, Type_of_execute typeofexecute)
        {
            string sql = "";
            switch (typeofexecute)
            {
                case Type_of_execute.count:
                    sql = "select count(*) from " + tablename;
                    break;
            }
            SqlDataReader sdr = null;
            try
            {
                sdr = DBHelper.ExecuteReader(sql, CommandType.Text, null);
                if (sdr.Read())
                {
                    int count = Convert.ToInt32(sdr[0]);
                    sdr.Close();
                    return count;
                }
                else
                {
                    sdr.Close();
                    return 0;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return 0;
            }
        }
        /// <summary>
        /// According to the column name do ascending and descending order
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="top_number">how many top to do</param>
        /// <param name="count_by_column">top column name</param>
        /// <param name="order_by_column">order column</param>
        /// <param name="typeofexecute">asc or desc</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string tablename, int top_number, string count_by_column, string order_by_column, Type_of_execute typeofexecute)
        {
            string sql = "";
            int topnumber = (top_number == 0) ? DatabaseSelect(tablename, Type_of_execute.count) : top_number;
            switch (typeofexecute)
            {
                case Type_of_execute.topasc:
                    sql = "select top " + topnumber + " " + count_by_column + " from " + tablename + " order by " + order_by_column + " asc ";
                    break;
                case Type_of_execute.topdesc:
                    sql = "select top " + topnumber + " " + count_by_column + " from " + tablename + " order by " + order_by_column + " desc ";
                    break;
            }
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }
        /// <summary>
        /// According to the condition inquires the total
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="typeofexecute">count function</param>
        /// <param name="conditions">conditions array</param>
        /// <returns>int</returns>
        public static int DatabaseSelect(string tablename, Type_of_execute typeofexecute, string[] conditions)
        {
            string sql = "";
            switch (typeofexecute)
            {
                case Type_of_execute.count:
                    sql = "select count(*) from " + tablename + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
                    break;

            }
            SqlDataReader sdr = null;
            try
            {
                sdr = DBHelper.ExecuteReader(sql, CommandType.Text, null);
                if (sdr.Read())
                {
                    int count = Convert.ToInt32(sdr[0]);
                    sdr.Close();
                    return count;
                }
                else
                {
                    sdr.Close();
                    return 0;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return 0;
            }
        }
        /// <summary>
        /// According to the condition and the column name do ascending and descending order
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="top_number">how many top to do</param>
        /// <param name="count_by_column">top column name</param>
        /// <param name="order_by_column">order column</param>
        /// <param name="typeofexecute">asc or desc</param>
        /// <param name="conditions">conditions array</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string tablename, int top_number, string count_by_column, string order_by_column, Type_of_execute typeofexecute, string[] conditions)
        {
            string sql = "";
            int topnumber = (top_number == 0) ? DatabaseSelect(tablename, Type_of_execute.count) : top_number;
            switch (typeofexecute)
            {
                case Type_of_execute.topasc:
                    sql = "select top " + topnumber + " " + count_by_column + " from " + tablename + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and) + " order by " + order_by_column + " asc ";
                    break;
                case Type_of_execute.topdesc:
                    sql = "select top " + topnumber + " " + count_by_column + " from " + tablename + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and) + " order by " + order_by_column + " desc ";
                    break;
            }
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        /// <summary>
        /// Many table shows joint inquires table structure
        /// </summary>
        public enum many_table_joint_inquires_switch
        {
            all,
            left,
            right
        }
        /// <summary>
        /// Many table joint inquires
        /// </summary>
        /// <param name="left_table">left table</param>
        /// <param name="right_table">right table</param>
        /// <param name="left_column">left column</param>
        /// <param name="right_column">right column</param>
        /// <param name="mtjis">Many table shows joint inquires table structure</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string left_table, string right_table, string left_column, string right_column, many_table_joint_inquires_switch mtjis)
        {
            string sql = "";
            switch (mtjis)
            {
                case many_table_joint_inquires_switch.all:
                    sql = "select * from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column;
                    break;
                case many_table_joint_inquires_switch.left:
                    sql = "select " + left_table + ".* from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column;
                    break;
                case many_table_joint_inquires_switch.right:
                    sql = "select " + right_table + ".* from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column;
                    break;
                default:
                    break;
            }
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }
        /// <summary>
        /// Take the table more joint inquires conditions
        /// </summary>
        /// <param name="left_table">left table</param>
        /// <param name="right_table">right table</param>
        /// <param name="left_column">left column</param>
        /// <param name="right_column">right column</param>
        /// <param name="mtjis">Many table shows joint inquires table structure</param>
        /// <param name="conditions">condition array</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string left_table, string right_table, string left_column, string right_column, many_table_joint_inquires_switch mtjis, string[] conditions)
        {
            string sql = "";
            switch (mtjis)
            {
                case many_table_joint_inquires_switch.all:
                    sql = "select * from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
                    break;
                case many_table_joint_inquires_switch.left:
                    sql = "select " + left_table + ".* from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
                    break;
                case many_table_joint_inquires_switch.right:
                    sql = "select " + right_table + ".* from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
                    break;
                default:
                    break;
            }
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }
        /// <summary>
        /// With a sort of more joint inquires table
        /// </summary>
        /// <param name="left_table">left table</param>
        /// <param name="right_table">right table</param>
        /// <param name="left_column">left column</param>
        /// <param name="right_column">right column</param>
        /// <param name="mtjis">Many table shows joint inquires table structure</param>
        /// <param name="conditions">condition array</param>
        /// <param name="order_column">order array</param>
        /// <param name="desc_or_asc">desc or asc</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string left_table, string right_table, string left_column, string right_column, many_table_joint_inquires_switch mtjis, string[] conditions, string order_column, string desc_or_asc)
        {
            string sql = "";
            switch (mtjis)
            {
                case many_table_joint_inquires_switch.all:
                    sql = "select * from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and) + " order by " + order_column + " " + desc_or_asc;
                    break;
                case many_table_joint_inquires_switch.left:
                    sql = "select " + left_table + ".* from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and) + " order by " + order_column + " " + desc_or_asc;
                    break;
                case many_table_joint_inquires_switch.right:
                    sql = "select " + right_table + ".* from " + left_table + " inner join " + right_table + " on " + left_table + "." + left_column + "=" + right_table + "." + right_column + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and) + " order by " + order_column + " " + desc_or_asc;
                    break;
                default:
                    break;
            }
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        /// <summary>
        /// Get all the data from the remote database
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="temp">true</param>
        /// <returns>dataset</returns>
        public static DataTable DatabaseSelect(string tablename, bool temp)
        {
            string sql = "select * from " + tablename;
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null, true);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return null;
            }
        }

        /// <summary>
        /// According to the condition from remote database get data
        /// </summary>
        /// <param name="column"></param>
        /// <param name="tablename"></param>
        /// <param name="temp"></param>
        /// <returns></returns>
        public static DataTable DatabaseSelect(string[] column, string tablename, bool temp)
        {
            string sql = "select " + Get_name_array_sum(column, Get_name_array_sum_switch.to_common) + "from " + tablename;
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null, true);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return null;
            }

        }

        public static DataTable DatabaseSelect(string tablename, string[] conditions, bool temp)
        {
            string sql = "select * from " + tablename + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
            try
            {
                return DBHelper.DataAdapter(sql, CommandType.Text, null, true);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }

        /// <summary>
        /// Virtual methods used in subclasses to achieve
        /// </summary>
        /// <returns>dataset</returns>
        public virtual DataTable DatabaseSelect()
        {
            return null;
        }

        #endregion

        #region The SQL statement used to generate the comprehensive conditions generic method

        /// <summary>
        /// Conditions relational expression
        /// </summary>
        public enum condition
        {
            eql,
            more,
            less,
            uneql
        }
        /// <summary>
        /// Get conditional statement
        /// </summary>
        /// <param name="left_hand_member">Relationship between the left</param>
        /// <param name="right_hand_member">Relationship between the right</param>
        /// <param name="condition">condition</param>
        /// <returns>string</returns>
        public static string equipollence(string left_hand_member, string right_hand_member, condition condition)
        {
            string sql = "";
            switch (condition)
            {
                case condition.eql:
                    sql = left_hand_member + "='" + right_hand_member + "'";
                    break;
                case condition.more:
                    sql = left_hand_member + ">'" + right_hand_member + "'";
                    break;
                case condition.less:
                    sql = left_hand_member + "<'" + right_hand_member + "'";
                    break;
                case condition.uneql:
                    sql = left_hand_member + "!='" + right_hand_member + "'";
                    break;
            }
            return sql;
        }
        /// <summary>
        /// For many of the joint inquires table conditional statement
        /// </summary>
        /// <param name="left_table">left table</param>
        /// <param name="left_hand_member">left mamber</param>
        /// <param name="right_table">right table</param>
        /// <param name="right_hand_member">right member</param>
        /// <param name="condition">condition</param>
        /// <returns>string</returns>
        public static string equipollence(string left_table, string left_hand_member, string right_hand_member, condition condition)
        {
            string sql = "";
            switch (condition)
            {
                case condition.eql:
                    sql = left_table + "." + left_hand_member + "='" + right_hand_member + "'";
                    break;
                case condition.more:
                    sql = left_table + "." + left_hand_member + ">'" + right_hand_member + "'";
                    break;
                case condition.less:
                    sql = left_table + "." + left_hand_member + "<'" + right_hand_member + "'";
                    break;
                case condition.uneql:
                    sql = left_table + "." + left_hand_member + "!='" + right_hand_member + "'";
                    break;
            }
            return sql;
        }

        #endregion

        #region For performing the function database insert into, update, delete the base class

        /// <summary>
        /// insert to the database
        /// </summary>
        /// <param name="tablename">table name</param>
        /// <param name="list_of_column_vlaue">data list</param>
        /// <returns>bool</returns>
        public static bool DatabaseInsert(string tablename, List<string[]> list_of_column_vlaue)
        {
            string sql = "";
            bool executive_outcomes = new bool();
            sql = "insert into " + tablename + " (" + link_column_to_value_for_insert(list_of_column_vlaue)[0] + ") values (" + link_column_to_value_for_insert(list_of_column_vlaue)[1] + ")";
            try
            {
                return executive_outcomes = DBHelper.ExecuteNonQuery(sql, CommandType.Text, null) == 1 ? true : false;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }
        }

        /// <summary>
        /// Convert the SQL statement
        /// </summary>
        /// <param name="list_of_column_vlaue">data list</param>
        /// <returns>string array</returns>
        private static string[] link_column_to_value_for_insert(List<string[]> list_of_column_vlaue)
        {
            string[] sql_key_value_list;
            string sql_column = "";
            string sql_value = "";
            for (int i = 0; i < list_of_column_vlaue.Count; i++)
            {
                sql_column += list_of_column_vlaue[i][0] + ", ";
                sql_value += "'" + list_of_column_vlaue[i][1] + "', ";
            }
            sql_column = sql_column.Substring(0, sql_column.Length - 2);
            sql_value = sql_value.Substring(0, sql_value.Length - 2);
            return sql_key_value_list = new string[2] { sql_column, sql_value };
        }

        /// <summary>
        /// update to the database
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="list_of_cloumn_value"></param>
        /// <returns></returns>
        public static bool DatabaseUpdate(string tablename, List<string[]> list_of_cloumn_value, string[] conditions)
        {
            string sql = "";
            bool executive_outcomes = new bool();
            sql = "update " + tablename + " set " + link_column_to_value_for_update(list_of_cloumn_value) + " where " + Get_name_array_sum(conditions, Get_name_array_sum_switch.to_and);
            try
            {
                return executive_outcomes = DBHelper.ExecuteNonQuery(sql, CommandType.Text, null) == 1 ? true : false;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }
        }

        /// <summary>
        /// Convert the SQL statement
        /// </summary>
        /// <param name="list_of_column_vlaue">data list</param>
        /// <returns>string array</returns>
        private static string link_column_to_value_for_update(List<string[]> list_of_column_vlaue)
        {
            string update_key_value = "";
            for (int i = 0; i < list_of_column_vlaue.Count; i++)
            {
                update_key_value += list_of_column_vlaue[i][0] + "='" + list_of_column_vlaue[i][1] + "', ";
            }
            update_key_value = update_key_value.Substring(0, update_key_value.Length - 2);
            return update_key_value;
        }

        #endregion

        public static void dosqlforsb(string sql)
        {
            DBHelper.DataAdapter(sql, CommandType.Text, null, true);
        }
        public static void cleartable()
        {
            string sql = "truncate table CarInfo";
            DBHelper.DataAdapter(sql, CommandType.Text, null, true);
        }
        public static void cleartable(string tablename)
        {
            string sql = "truncate table " + tablename;
            DBHelper.DataAdapter(sql, CommandType.Text, null, true);
        }
    }
}
